package com.geologyoa.persist;

import java.io.File;
import java.io.StringWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

import javax.servlet.ServletConfig;
import javax.servlet.ServletContext;

import org.dom4j.Document;
import org.dom4j.DocumentHelper;
import org.dom4j.Element;
import org.dom4j.io.XMLWriter;

import com.geologyoa.commons.ExcelOperate;
import com.geologyoa.commons.JdbcUtil;
import com.geologyoa.commons.commUtils;
import com.geologyoa.domain.Wpdj01;
import com.geologyoa.domain.Wpfj01;
import com.sun.xml.internal.bind.CycleRecoverable.Context;

public class Wpdj01DaoImpl implements Wpdj01Dao {
	Connection conn = null;
	PreparedStatement ps = null;
	ResultSet rs = null;
	public Boolean CheckWpdj(String WPDJ0107) {
		Boolean flag =false;
		conn=JdbcUtil.getConnection();
		String sql = "";
		sql = "select * from wpdj01 where WPDj0107=?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, WPDJ0107);
			// modify by cuiyi 20130331   精简代码
			ResultSet rs = ps.executeQuery();
			flag = rs.next();
			/*int number=ps.executeUpdate();
			if(number>0)
			{
				flag=true;
			}*/
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			JdbcUtil.closeAll(ps,conn);
		}
		return flag;
	}
	
	public Boolean CheckWpdjByNameExceptId(int id, String name) {
		Boolean flag =false;
		conn=JdbcUtil.getConnection();
		String sql = "";
		sql = "select * from wpdj01 where WPDJ0101!=? and WPDJ0107=? ";
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			ps.setString(2, name);
			ResultSet rs = ps.executeQuery();
			flag = rs.next();
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			JdbcUtil.closeAll(ps,conn);
		}
		return flag;
	}

	public Boolean AddWpdj(Wpdj01 wpdj01) {
		Boolean flag =false;
		   conn=JdbcUtil.getConnection();
		   Calendar cal = Calendar.getInstance();
		   int year = cal.get(Calendar.YEAR);
		   String tmpYear = wpdj01.getWpdj0105()==null||wpdj01.getWpdj0105().equals("")?String.valueOf(year):wpdj01.getWpdj0105();
		   String sql = "select  wpdj0102 from WPDJ01 where wpdj0102 like 'WP"+tmpYear+"____' order by wpdj0102 desc ";
		  String wpdj0102 = "";
		  try {
				ps = conn.prepareStatement(sql);
				ResultSet rs = ps.executeQuery();
				if(rs.next()){
					String maxCode = rs.getString("wpdj0102");
					int tmpCode =Integer.parseInt(maxCode.substring(6))+1;
					wpdj0102 = maxCode.substring(0, 6)+commUtils.completStr(String.valueOf(tmpCode), 4);
				}else{
					wpdj0102 = "WP"+tmpYear+"0001";
				}
				sql = "insert into wpdj01 values(oa_seq.nextval,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
				ps = conn.prepareStatement(sql);
				ps.setString(1, wpdj0102);
				ps.setString(2, wpdj01.getWpdj0103());
				ps.setString(3, wpdj01.getWpdj0104());
				ps.setString(4, wpdj01.getWpdj0105());
				ps.setString(5, wpdj01.getWpdj0106());
				ps.setString(6, wpdj01.getWpdj0107());
				ps.setString(7, wpdj01.getWpdj0108());
				ps.setDouble(8, wpdj01.getWpdj0109());
				ps.setDouble(9, wpdj01.getWpdj0110());
				ps.setString(10, wpdj01.getWpdj0111());
				ps.setString(11, wpdj01.getWpdj0112());
				ps.setDouble(12, wpdj01.getWpdj0113());
				ps.setString(13, wpdj01.getWpdj0114());
				ps.setString(14, wpdj01.getWpdj0115());
				ps.setString(15, wpdj01.getWpdj0116());
				ps.setString(16, wpdj01.getWpdj0117());
				ps.setString(17, wpdj01.getWpdj0118());
				ps.setString(18, wpdj01.getWpdj0119());
				ps.setString(19, wpdj01.getWpdj0120());
				ps.setString(20, wpdj01.getWpdj0121());
				ps.setString(21, wpdj01.getWpdj0122());
				ps.setString(22, wpdj01.getWpdj0123());
				ps.setString(23, wpdj01.getWpdj0124());
				ps.setString(24, wpdj01.getWpdj0125());
				ps.setString(25, wpdj01.getWpdj0126());
				ps.setString(26, wpdj01.getWpdj0127());
				ps.setString(27, wpdj01.getWpdj0128());
				ps.setString(28, wpdj01.getWpdj0129());
				ps.setString(29, wpdj01.getWpdj0130());
				ps.setString(30, wpdj01.getWpdj0131());
				ps.setDouble(31, wpdj01.getWpdj0132());
				ps.setDouble(32, wpdj01.getWpdj0133());
				ps.setDouble(32, wpdj01.getWpdj0133());
				ps.setString(33, wpdj01.getWpdj0134());
				int number=ps.executeUpdate();
				if(number>0)
				{
					////危评登记录入成功，开始录入灾害点列表
					sql = "select WPDJ0101 from WPDJ01 where WPDJ0107='"+wpdj01.getWpdj0107()+"' order by WPDJ0101 desc";
					ps = conn.prepareStatement(sql);
					rs=ps.executeQuery();
					int bgId = 0;
					if(rs.next())
						bgId = rs.getInt(1);
					if(bgId==0){
						System.out.println("危评报告录入失败");
						return false;
					}
					if(!wpdj01.getRealPath().equals("")){
						try{
							File file = new File(wpdj01.getRealPath());
							String[][] data = ExcelOperate.getData(file, 1);
							for(int i=0;i<data.length;i++) {
								sql = "insert into wptj01 values(oa_seq.nextval,?,?,?,?,?)";
								ps = conn.prepareStatement(sql);
								ps.setString(1, wpdj0102);
								ps.setString(2, data[i][0]);
								ps.setString(3, data[i][1]);
								ps.setString(4, data[i][2]);
								ps.setInt(5, bgId);
								ps.executeUpdate();
					       }
						}catch (Exception e) {
							System.out.println("读取拐点excel失败");
							// TODO: handle exception
						}
					}
					flag=true;
				}
				
			} catch (SQLException e) {
				e.printStackTrace();
			} finally
			{
				JdbcUtil.closeAll(ps,conn);
			}
			return flag;
	}
	/*public List<Wpdj01> SerachWpdj(String WPDJ0102,String WPDJ0103, String WPDJ0104,
			String WPDJ0105, String WPDJ0107, String WPDJ0108, String WPDJ0111,
			String WPDJ0113min, String WPDJ0113max, String WPDJ0114,
			String WPDJ0116, String WPDJ0119s, String WPDJ0119e,String WPDJ0122s,String WPDJ0122e,String WPDJ0118) {
			Wpdj01 wpdj01=null;
			List<Wpdj01> wpdf01List=new ArrayList<Wpdj01>();
			conn=JdbcUtil.getConnection();
			StringBuffer sqlHasAllUnit = new StringBuffer();//匹配带有建设单位和设计单位
			StringBuffer sqlNoUnit  = new StringBuffer();//匹配不带有任何单位的危评。
			StringBuffer sqlHasDesignUintOnly = new StringBuffer();
			StringBuffer sqlHasBuildUnitOnly = new StringBuffer();
			StringBuffer commonSql = new StringBuffer();
			boolean jsdwFlg = false;
			boolean wpdwFlg = false;
		   //建设单位和危评单位同时存在
			sqlHasAllUnit.append("select b.wpdj0101, " +
					"b.wpdj0102, b.wpdj0103, " +
					"b.wpdj0104, b.wpdj0105, " +
					"b.wpdj0106, b.wpdj0107, b.wpdj0108, " +
					"b.wpdj0109, b.wpdj0110, b.wpdj0111," +
					" b.wpdj0112, b.wpdj0113, b.wpdj0114," +
					" b.dwqk0103 as wpdj0114name, b.dwqk0105 as wpdj0115," +
					" b.wpdj0116, a.dwqk0103 as wpdj0116name," +
					"a.dwqk0105 as wpdj0117,c.dwzz0105 as wpdj0118,b.wpdj0119," +
					"b.wpdj0120,b.wpdj0121,b.wpdj0122," +
					"b.wpdj0123,b.wpdj0124,b.wpdj0125," +
					"b.wpdj0126,b.wpdj0127,b.wpdj0128, " +
					"b.wpdj0129,b.wpdj0130,b.wpdj0131,b.wpdj0132,b.wpdj0133,b.wpdj0134 " +
					"from dwqk01 a,dwzz01 c, " +
					"(select * from wpdj01,dwqk01 where  wpdj01.wpdj0114=dwqk01.dwqk0101 ) b" +
					" where a.dwqk0101 = b.wpdj0116 and a.dwqk0101 = c.dwzz0102 and c.dwzz0104='危评'");
			////建设单位和危评单位同时不存在
			 sqlNoUnit.append(" UNION select b.wpdj0101, " +
						"b.wpdj0102, b.wpdj0103, " +
						"b.wpdj0104, b.wpdj0105, " +
						"b.wpdj0106, b.wpdj0107, b.wpdj0108, " +
						"b.wpdj0109, b.wpdj0110, b.wpdj0111," +
						" b.wpdj0112, b.wpdj0113, b.wpdj0114," +
						" b.wpdj0114 as wpdj0114name, b.wpdj0115," +
						" b.wpdj0116, b.wpdj0116 as wpdj0116name," +
						"b.wpdj0117,b.wpdj0118,b.wpdj0119," +
						"b.wpdj0120,b.wpdj0121,b.wpdj0122," +
						"b.wpdj0123,b.wpdj0124,b.wpdj0125," +
						"b.wpdj0126,b.wpdj0127,b.wpdj0128, " +
						"b.wpdj0129,b.wpdj0130,b.wpdj0131,b.wpdj0132,b.wpdj0133,b.wpdj0134 " +
						"from wpdj01 b where (b.wpdj0114 is  null and  b.wpdj0116 is  null )");
			 ////只存在危评单位
			 sqlHasDesignUintOnly.append("  UNION  select b.wpdj0101, " +
						"b.wpdj0102, b.wpdj0103, " +
						"b.wpdj0104, b.wpdj0105, " +
						"b.wpdj0106, b.wpdj0107, b.wpdj0108, " +
						"b.wpdj0109, b.wpdj0110, b.wpdj0111," +
						" b.wpdj0112, b.wpdj0113, b.wpdj0114," +
						" b.wpdj0114 as wpdj0114name, b.wpdj0115," +
						" b.wpdj0116, a.dwqk0103 as wpdj0116name," +
						"a.dwqk0105 as wpdj0117,c.dwzz0105 as wpdj0118,b.wpdj0119," +
						"b.wpdj0120,b.wpdj0121,b.wpdj0122," +
						"b.wpdj0123,b.wpdj0124,b.wpdj0125," +
						"b.wpdj0126,b.wpdj0127,b.wpdj0128, " +
						"b.wpdj0129,b.wpdj0130,b.wpdj0131,b.wpdj0132,b.wpdj0133,b.wpdj0134 " +
						"from dwqk01 a,dwzz01 c, " +
						"wpdj01 b" +
						" where a.dwqk0101 = b.wpdj0116 and a.dwqk0101 = c.dwzz0102 and c.dwzz0104='危评' and b.wpdj0114 is null");
			 ////只存在建设单位
			 sqlHasBuildUnitOnly.append("  UNION select wpdj0101, " +
						"wpdj0102, wpdj0103, " +
						"wpdj0104, wpdj0105, " +
						"wpdj0106, wpdj0107, wpdj0108, " +
						"wpdj0109, wpdj0110, wpdj0111," +
						" wpdj0112, wpdj0113, wpdj0114," +
						" dwqk0103 as wpdj0114name, dwqk0105 as wpdj0115," +
						" wpdj0116, wpdj0116 as wpdj0116name," +
						"wpdj0117,wpdj0118,wpdj0119," +
						"wpdj0120,wpdj0121,wpdj0122," +
						"wpdj0123,wpdj0124,wpdj0125," +
						"wpdj0126,wpdj0127,wpdj0128, " +
						"wpdj0129,wpdj0130,wpdj0131,b.wpdj0132,b.wpdj0133,b.wpdj0134 " +
						"from wpdj01 b,dwqk01 a where  b.wpdj0114=a.dwqk0101  and b.wpdj0116 is null ");
		   ///匹配建设单位名称模糊查询条件
		   if(!("").equals(WPDJ0114)){
			   sqlHasAllUnit.append(" and b.dwqk0103 like '%"+WPDJ0114+"%'");
			   sqlHasBuildUnitOnly.append(" and dwqk0103 like '%"+WPDJ0114+"%'");
			  
			   jsdwFlg = true;
		   }
		   ///匹配危评单位名称模糊查询条件
		   if(!("").equals(WPDJ0116)){
			  
			   sqlHasAllUnit.append(" and a.dwqk0103  like '%"+WPDJ0116+"%'");
			   sqlHasDesignUintOnly.append(" and a.dwqk0103  like '%"+WPDJ0116+"%'");
			   wpdwFlg = true;
			   
		   }
		   if(!("").equals(WPDJ0118)&&!WPDJ0118.equals("0")){
			   //WPDJ0118 = WPDJ0118.equals("0")?"":WPDJ0118;
			   sqlHasAllUnit.append(" and c.DWZZ0105 like '%"+WPDJ0118+"%'");
			   sqlHasDesignUintOnly.append(" and  c.DWZZ0105 like '%"+WPDJ0118+"%'");
			   wpdwFlg = true;
			   
		   }
		   
		 ///所有情况都需要的查询条件
		   if(!("").equals(WPDJ0102)){
			   commonSql.append(" and b.WPDJ0102 like'%"+WPDJ0102+"%'");
		   }
		   if(!("").equals(WPDJ0103)){
			   commonSql.append(" and b.WPDJ0103 ='"+WPDJ0103+"'");
		   }
		   if(!("").equals(WPDJ0104)){
			   commonSql.append(" and b.WPDJ0104 ='"+WPDJ0104+"'");
		   }
		   if(!("").equals(WPDJ0105)){
			   commonSql.append(" and b.WPDJ0105 ='"+WPDJ0105+"'");
		   }
		   if(!("").equals(WPDJ0107)){
			   commonSql.append(" and b.WPDJ0107 like '%"+WPDJ0107+"%'");
		   }
		   if(!("").equals(WPDJ0108)){
			   commonSql.append(" and b.WPDJ0108 ='"+WPDJ0108+"'");
		   }
		   if(!("").equals(WPDJ0111)){
			   commonSql.append(" and b.WPDJ0111 ='"+WPDJ0111+"'");
		   }
		   if(!("").equals(WPDJ0113min)){
			   commonSql.append(" and b.WPDJ0113 >='"+WPDJ0113min+"'");
		   }
		   if(!("").equals(WPDJ0113max)){
			   commonSql.append(" and b.WPDJ0113 <'"+WPDJ0113max+"'");
		   }
		  
		   if(!("").equals(WPDJ0119s)){
			   commonSql.append(" and b.WPDJ0119 >='"+WPDJ0119s+"'");
		   }
		   if(!("").equals(WPDJ0119e)){
			   commonSql.append(" and b.WPDJ0119 <'"+WPDJ0119e+"'");
		   }
		   //////// ADD BY  CUIYI 20130509
		   if(WPDJ0122s!=null&&!("").equals(WPDJ0122s)){
			   commonSql.append(" and b.WPDJ0122 >='"+WPDJ0122s+"'");
		   }
		   if(WPDJ0122e!=null&&!("").equals(WPDJ0122e)){
			   commonSql.append(" and b.WPDJ0122 <'"+WPDJ0122e+"'");
		   }
		   sqlHasAllUnit.append(commonSql);
		   sqlHasBuildUnitOnly.append(commonSql);
		   sqlHasDesignUintOnly.append(commonSql);
		   sqlNoUnit.append(commonSql);
			try {
				if(wpdwFlg||jsdwFlg){
					if(wpdwFlg){
						ps = conn.prepareStatement(sqlHasAllUnit.toString()+sqlHasDesignUintOnly.toString());
					}else if(jsdwFlg){
						ps = conn.prepareStatement(sqlHasAllUnit.toString()+sqlHasBuildUnitOnly.toString());
					}else{
						ps = conn.prepareStatement(sqlHasAllUnit.toString()+sqlHasDesignUintOnly.toString()
								+sqlHasBuildUnitOnly.toString());
					}
				}else{
					//建设单位和评估单位  条件都为空
					ps = conn.prepareStatement(sqlHasAllUnit.toString()
							+sqlNoUnit.toString()+sqlHasBuildUnitOnly.toString()
							+sqlHasDesignUintOnly.toString());
				}
				rs=ps.executeQuery();
				while(rs.next())
				{
					wpdj01=new Wpdj01();
					wpdj01.setWpdj0101(Integer.parseInt(rs.getString("wpdj0101")));
					wpdj01.setWpdj0102(rs.getString("wpdj0102"));
					wpdj01.setWpdj0103(rs.getString("wpdj0103"));
					wpdj01.setWpdj0104(rs.getString("wpdj0104"));
					wpdj01.setWpdj0105(rs.getString("wpdj0105"));
					wpdj01.setWpdj0106(rs.getString("wpdj0106"));
					wpdj01.setWpdj0107(rs.getString("wpdj0107"));
					wpdj01.setWpdj0108(rs.getString("wpdj0108"));
					wpdj01.setWpdj0109(rs.getDouble("wpdj0109"));
					wpdj01.setWpdj0110(rs.getDouble("wpdj0110"));
					wpdj01.setWpdj0111(rs.getString("wpdj0111"));
					wpdj01.setWpdj0112(rs.getString("wpdj0112"));
					wpdj01.setWpdj0113(rs.getDouble("wpdj0113"));
					wpdj01.setWpdj0114(rs.getString("wpdj0114"));
					wpdj01.setWpdj0115(rs.getString("wpdj0115"));
					wpdj01.setWpdj0116(rs.getString("wpdj0116"));
					wpdj01.setWpdj0117(rs.getString("wpdj0117"));
					wpdj01.setWpdj0118(rs.getString("wpdj0118"));
					wpdj01.setWpdj0119(rs.getString("wpdj0119")==null?"":rs.getString("wpdj0119"));
					wpdj01.setWpdj0120(rs.getString("wpdj0120"));
					wpdj01.setWpdj0121(rs.getString("wpdj0121"));
					wpdj01.setWpdj0122(rs.getString("wpdj0122")==null?"":rs.getString("wpdj0122"));
					wpdj01.setWpdj0123(rs.getString("wpdj0123"));
					wpdj01.setWpdj0124(rs.getString("wpdj0124"));
					wpdj01.setWpdj0125(rs.getString("wpdj0125"));
					wpdj01.setWpdj0126(rs.getString("wpdj0126"));
					wpdj01.setWpdj0127(rs.getString("wpdj0127"));
					wpdj01.setWpdj0128(rs.getString("wpdj0128"));
					wpdj01.setWpdj0129(rs.getString("wpdj0129"));
					wpdj01.setWpdj0130(rs.getString("wpdj0130"));
					wpdj01.setWpdj0131(rs.getString("wpdj0131"));
					wpdj01.setWpdj0132(rs.getDouble("wpdj0132"));
					wpdj01.setWpdj0133(rs.getDouble("wpdj0133"));
					wpdj01.setWpdj0134(rs.getString("wpdj0134"));
					wpdj01.setWpdj0114name(rs.getString("wpdj0114name"));
					wpdj01.setWpdj0116name(rs.getString("wpdj0116name"));
					wpdf01List.add(wpdj01);
				}
				
			} catch (SQLException e) {
				e.printStackTrace();
			} finally
			{
				JdbcUtil.closeAll(ps,conn);
			}
			return wpdf01List;
	}*/
	public List<Wpdj01> SerachWpdj(String WPDJ0102,String WPDJ0103, String WPDJ0104,
			String WPDJ0105, String WPDJ0107, String WPDJ0108, String WPDJ0111,
			String WPDJ0113min, String WPDJ0113max, String WPDJ0114,
			String WPDJ0116, String WPDJ0119s, String WPDJ0119e,String WPDJ0122s,String WPDJ0122e,String WPDJ0118) {
			Wpdj01 wpdj01=null;
			List<Wpdj01> wpdf01List=new ArrayList<Wpdj01>();
			conn=JdbcUtil.getConnection();
			StringBuffer sqlHasAllUnit = new StringBuffer();//匹配带有建设单位和设计单位
			StringBuffer commonSql = new StringBuffer();
		   
			sqlHasAllUnit.append("select b.wpdj0101, " +
					"b.wpdj0102, b.wpdj0103, " +
					"b.wpdj0104, b.wpdj0105, " +
					"b.wpdj0106, b.wpdj0107, b.wpdj0108, " +
					"b.wpdj0109, b.wpdj0110, b.wpdj0111," +
					" b.wpdj0112, b.wpdj0113, b.wpdj0114," +
					" b.wpdj0114 as wpdj0114name, b.wpdj0115 as wpdj0115," +
					" b.wpdj0116, a.dwqk0103 as wpdj0116name," +
					"a.dwqk0105 as wpdj0117,c.dwzz0105 as wpdj0118,b.wpdj0119," +
					"b.wpdj0120,b.wpdj0121,b.wpdj0122," +
					"b.wpdj0123,b.wpdj0124,b.wpdj0125," +
					"b.wpdj0126,b.wpdj0127,b.wpdj0128, " +
					"b.wpdj0129,b.wpdj0130,b.wpdj0131,b.wpdj0132,b.wpdj0133,b.wpdj0134 " +
					" from wpdj01 b,dwqk01 a,dwzz01 c " +
					" where a.dwqk0101 = b.wpdj0116 and a.dwqk0101 = c.dwzz0102 and c.dwzz0104='危评'");
			
		   
		 ///所有情况都需要的查询条件
		   if(!("").equals(WPDJ0102)){
			   commonSql.append(" and b.WPDJ0102 like'%"+WPDJ0102+"%'");
		   }
		   if(!("").equals(WPDJ0103)){
			   commonSql.append(" and b.WPDJ0103 ='"+WPDJ0103+"'");
		   }
		   if(!("").equals(WPDJ0104)){
			   commonSql.append(" and b.WPDJ0104 ='"+WPDJ0104+"'");
		   }
		   if(!("").equals(WPDJ0105)){
			   commonSql.append(" and b.WPDJ0105 ='"+WPDJ0105+"'");
		   }
		   if(!("").equals(WPDJ0107)){
			   commonSql.append(" and b.WPDJ0107 like '%"+WPDJ0107+"%'");
		   }
		   if(!("").equals(WPDJ0108)){
			   commonSql.append(" and b.WPDJ0108 ='"+WPDJ0108+"'");
		   }
		   if(!("").equals(WPDJ0111)){
			   commonSql.append(" and b.WPDJ0111 ='"+WPDJ0111+"'");
		   }
		   if(!("").equals(WPDJ0113min)){
			   commonSql.append(" and b.WPDJ0113 >='"+WPDJ0113min+"'");
		   }
		   if(!("").equals(WPDJ0113max)){
			   commonSql.append(" and b.WPDJ0113 <'"+WPDJ0113max+"'");
		   }
		  
		   if(!("").equals(WPDJ0119s)){
			   commonSql.append(" and b.WPDJ0119 >='"+WPDJ0119s+"'");
		   }
		   if(!("").equals(WPDJ0119e)){
			   commonSql.append(" and b.WPDJ0119 <'"+WPDJ0119e+"'");
		   }
		   //////// ADD BY  CUIYI 20130509
		   if(WPDJ0122s!=null&&!("").equals(WPDJ0122s)){
			   commonSql.append(" and b.WPDJ0122 >='"+WPDJ0122s+"'");
		   }
		   if(WPDJ0122e!=null&&!("").equals(WPDJ0122e)){
			   commonSql.append(" and b.WPDJ0122 <'"+WPDJ0122e+"'");
		   }
		   if(WPDJ0114!=null&&!("").equals(WPDJ0114)){
			   commonSql.append(" and b.WPDJ0114 like '%"+WPDJ0114+"%'");
		   }
		   sqlHasAllUnit.append(commonSql);
		
			try {
				ps = conn.prepareStatement(sqlHasAllUnit.toString());
				rs=ps.executeQuery();
				while(rs.next()){
					wpdj01=new Wpdj01();
					wpdj01.setWpdj0101(Integer.parseInt(rs.getString("wpdj0101")));
					wpdj01.setWpdj0102(rs.getString("wpdj0102"));
					wpdj01.setWpdj0103(rs.getString("wpdj0103"));
					wpdj01.setWpdj0104(rs.getString("wpdj0104"));
					wpdj01.setWpdj0105(rs.getString("wpdj0105"));
					wpdj01.setWpdj0106(rs.getString("wpdj0106"));
					wpdj01.setWpdj0107(rs.getString("wpdj0107"));
					wpdj01.setWpdj0108(rs.getString("wpdj0108"));
					wpdj01.setWpdj0109(rs.getDouble("wpdj0109"));
					wpdj01.setWpdj0110(rs.getDouble("wpdj0110"));
					wpdj01.setWpdj0111(rs.getString("wpdj0111"));
					wpdj01.setWpdj0112(rs.getString("wpdj0112"));
					wpdj01.setWpdj0113(rs.getDouble("wpdj0113"));
					wpdj01.setWpdj0114(rs.getString("wpdj0114"));
					wpdj01.setWpdj0115(rs.getString("wpdj0115"));
					wpdj01.setWpdj0116(rs.getString("wpdj0116"));
					wpdj01.setWpdj0117(rs.getString("wpdj0117"));
					wpdj01.setWpdj0118(rs.getString("wpdj0118"));
					wpdj01.setWpdj0119(rs.getString("wpdj0119")==null?"":rs.getString("wpdj0119"));
					wpdj01.setWpdj0120(rs.getString("wpdj0120"));
					wpdj01.setWpdj0121(rs.getString("wpdj0121"));
					wpdj01.setWpdj0122(rs.getString("wpdj0122")==null?"":rs.getString("wpdj0122"));
					wpdj01.setWpdj0123(rs.getString("wpdj0123"));
					wpdj01.setWpdj0124(rs.getString("wpdj0124"));
					wpdj01.setWpdj0125(rs.getString("wpdj0125"));
					wpdj01.setWpdj0126(rs.getString("wpdj0126"));
					wpdj01.setWpdj0127(rs.getString("wpdj0127"));
					wpdj01.setWpdj0128(rs.getString("wpdj0128"));
					wpdj01.setWpdj0129(rs.getString("wpdj0129"));
					wpdj01.setWpdj0130(rs.getString("wpdj0130"));
					wpdj01.setWpdj0131(rs.getString("wpdj0131"));
					wpdj01.setWpdj0132(rs.getDouble("wpdj0132"));
					wpdj01.setWpdj0133(rs.getDouble("wpdj0133"));
					wpdj01.setWpdj0134(rs.getString("wpdj0134"));
					wpdj01.setWpdj0114name(rs.getString("wpdj0114name"));
					wpdj01.setWpdj0116name(rs.getString("wpdj0116name"));
					wpdf01List.add(wpdj01);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			} finally
			{
				JdbcUtil.closeAll(ps,conn);
			}
			return wpdf01List;
	}
	public Boolean DelWpdj(String wpdj0101) {
		Boolean flag =false;
		   conn=JdbcUtil.getConnection();
		   String sql = "";
		   sql = "delete from wpdj01  where wpdj0101=?";
			try {
				ps = conn.prepareStatement(sql);
				ps.setString(1, wpdj0101);
				int number=ps.executeUpdate();
				if(number>0)
				{
					flag=true;
				}
				
			} catch (SQLException e) {
				e.printStackTrace();
			} finally
			{
				JdbcUtil.closeAll(ps,conn);
			}
			return flag;
	}
	public Boolean UpdateWpdj(Wpdj01 wpdj01) {
		Boolean flag =false;
		   conn=JdbcUtil.getConnection();
		   String sql = "";
		   sql = "update wpdj01 set wpdj0102=?,Wpdj0103 =?,Wpdj0104=?,Wpdj0105=?," +
		   		"Wpdj0106=?,Wpdj0107=?,Wpdj0108=?,Wpdj0109=?,Wpdj0110=?," +
		   		"Wpdj0111=?,Wpdj0112=?,Wpdj0113=?,Wpdj0114=?,Wpdj0115=?," +
		   		"Wpdj0116=?,Wpdj0117=?,Wpdj0118=?,Wpdj0119=?,Wpdj0120=?," +
		   		"Wpdj0121=?,Wpdj0122=?,Wpdj0123=?,Wpdj0124=?,Wpdj0125=?," +
		   		"Wpdj0126=?,Wpdj0127=?,Wpdj0128=?,Wpdj0129=? ,Wpdj0130=?," +
		   		"Wpdj0131=?,Wpdj0132=?,Wpdj0133=?,Wpdj0134=? " +
		   		" where Wpdj0101=?";
			try {
				ps = conn.prepareStatement(sql);
				ps.setString(1, wpdj01.getWpdj0102());
				ps.setString(2, wpdj01.getWpdj0103());
				ps.setString(3, wpdj01.getWpdj0104());
				ps.setString(4, wpdj01.getWpdj0105());
				ps.setString(5, wpdj01.getWpdj0106());
				ps.setString(6, wpdj01.getWpdj0107());
				ps.setString(7, wpdj01.getWpdj0108());
				ps.setDouble(8, wpdj01.getWpdj0109());
				ps.setDouble(9, wpdj01.getWpdj0110());
				ps.setString(10, wpdj01.getWpdj0111());
				ps.setString(11, wpdj01.getWpdj0112());
				ps.setDouble(12, wpdj01.getWpdj0113());
				ps.setString(13, wpdj01.getWpdj0114());
				ps.setString(14, wpdj01.getWpdj0115());
				ps.setString(15, wpdj01.getWpdj0116());
				ps.setString(16, wpdj01.getWpdj0117());
				ps.setString(17, wpdj01.getWpdj0118());
				ps.setString(18, wpdj01.getWpdj0119());
				ps.setString(19, wpdj01.getWpdj0120());
				ps.setString(20, wpdj01.getWpdj0121());
				ps.setString(21, wpdj01.getWpdj0122());
				ps.setString(22, wpdj01.getWpdj0123());
				ps.setString(23, wpdj01.getWpdj0124());
				ps.setString(24, wpdj01.getWpdj0125());
				ps.setString(25, wpdj01.getWpdj0126());
				ps.setString(26, wpdj01.getWpdj0127());
				ps.setString(27, wpdj01.getWpdj0128());
				ps.setString(28, wpdj01.getWpdj0129());
				ps.setString(29, wpdj01.getWpdj0130());
				ps.setString(30, wpdj01.getWpdj0131());
				ps.setDouble(31, wpdj01.getWpdj0132());
				ps.setDouble(32, wpdj01.getWpdj0133());
				ps.setString(33, wpdj01.getWpdj0134());
				ps.setString(34, wpdj01.getWpdj0101().toString());
				int number=ps.executeUpdate();
				if(number>0)
				{
					if(!wpdj01.getRealPath().equals("")){
						try{
							
							////查询是否存在相同的危评拐点文件记录
							File file = new File(wpdj01.getRealPath());
							String[][] data = ExcelOperate.getData(file, 1);
							////表示为已有的拐点记录 需删除
							sql = "delete from wptj01 where wptj0106=?";
							ps = conn.prepareStatement(sql);
							ps.setInt(1, wpdj01.getWpdj0101());
							ps.executeUpdate();
							for(int i=0;i<data.length;i++) {
								sql = "insert into wptj01 values(oa_seq.nextval,?,?,?,?,?)";
								ps = conn.prepareStatement(sql);
								ps.setString(1, wpdj01.getWpdj0102());
								ps.setString(2, data[i][0]);
								ps.setString(3, data[i][1]);
								ps.setString(4, data[i][2]);
								ps.setInt(5, wpdj01.getWpdj0101());
								ps.executeUpdate();
					       }
						}catch (Exception e) {
							System.out.println("拐点excel更新失败");
							// TODO: handle exception
						}
					}
					flag=true;
				}
				
			} catch (SQLException e) {
				e.printStackTrace();
			} finally
			{
				JdbcUtil.closeAll(ps,conn);
			}
			return flag;
	}
	
	///type  0 代表所有  1：危评报告附件  2：备案登记表附件
	public  List<Wpfj01> SearchWpfj(String xmName, String wdName, String wdType) {
		String sql = "select wpdj0101, wpdj0102,wpdj0107,wpdj0127,wpdj0128,wpdj0129,wpdj0130,wpdj0131 " +
				"from wpdj01 where wpdj0107 like '%"+xmName+"%' ";
		List<Wpfj01> wpfj01List=new ArrayList<Wpfj01>();
		try{
			conn=JdbcUtil.getConnection();
			ps = conn.prepareStatement(sql);
			rs=ps.executeQuery();
			while(rs.next()){
				
				//Wpfj01 wpfj = new Wpfj01();
				int wpfj0101 = rs.getInt("wpdj0101");//id
				String wpfj0102 = rs.getString("wpdj0102");//总编号
				String wpfj0103 = rs.getString("wpdj0107");//评估报告名称
				String wpdj0127 = rs.getString("wpdj0127");//  1：危评报告附件
				String wpdj0128 = rs.getString("wpdj0128");//  2：备案登记表附件
				String wpdj0129 = rs.getString("wpdj0129");//  3：其它附件
				String wpdj0130 = rs.getString("wpdj0130");//  4：专家组专家个人意见（修改意见）
				String wpdj0131 = rs.getString("wpdj0131");//  5：专家组审查意见（含签名表）
				String type = "";////附件类型
				//wpfj.setWpfj0102(wpfj0102);//总编号
				//wpfj.setWpfj0103(wpfj0103);//评估报告名称
				/*if(wdName.equals("")){////如果检索文档名称为空的话
					if(wdType.equals("0")){
						wpfj.setType("1");
						wpfj.setWpfj0106(wpdj0127);
						wpfj01List.add(wpfj);
						wpfj.setType("2");
						wpfj.setWpfj0106(wpdj0128);
						wpfj01List.add(wpfj);
						wpfj.setType("3");
						wpfj.setWpfj0106(wpdj0129);
						wpfj01List.add(wpfj);
						wpfj.setType("4");
						wpfj.setWpfj0106(wpdj0130);
						wpfj01List.add(wpfj);
						wpfj.setType("5");
						wpfj.setWpfj0106(wpdj0131);
						wpfj01List.add(wpfj);
					}else{///检索的某一类文档
						switch(Integer.parseInt(wdType)){
						case 1:
							wpfj.setWpfj0106(wpdj0127);
							wpfj01List.add(wpfj);
							break;
						case 2:
							wpfj.setWpfj0106(wpdj0128);
							wpfj01List.add(wpfj);
							break;
						case 3:
							wpfj.setWpfj0106(wpdj0129);
							wpfj01List.add(wpfj);
							break;
						case 4:
							wpfj.setWpfj0106(wpdj0130);
							wpfj01List.add(wpfj);
							break;
						case 5:
							wpfj.setWpfj0106(wpdj0131);
							wpfj01List.add(wpfj);
							break;
						}
					}
				}else{///检索的文档名称不为空*/
				if(wdType.equals("0")){
					wpfj01List.add(getObject(wdName, wpdj0127, wpfj0102, wpfj0103, "1",wpfj0101));
					wpfj01List.add(getObject(wdName, wpdj0128, wpfj0102, wpfj0103, "2",wpfj0101));
					wpfj01List.add(getObject(wdName, wpdj0129, wpfj0102, wpfj0103, "3",wpfj0101));
					wpfj01List.add(getObject(wdName, wpdj0130, wpfj0102, wpfj0103, "4",wpfj0101));
					wpfj01List.add(getObject(wdName, wpdj0131, wpfj0102, wpfj0103, "5",wpfj0101));
					
					
					/*if((wpdj0128!=null&&wpdj0128.indexOf(wdName)>-1)||(wpdj0128==null&&wdName.equals(""))){
						wpfj = new Wpfj01();
						wpfj.setWpfj0102(wpfj0102);//总编号
						wpfj.setWpfj0103(wpfj0103);//评估报告名称
						wpfj.setType("2");
						wpfj.setWpfj0106(wpdj0128);
						wpfj01List.add(wpfj);
					}
					if((wpdj0129!=null&&wpdj0129.indexOf(wdName)>-1)||(wpdj0129==null&&wdName.equals(""))){
						wpfj = new Wpfj01();
						wpfj.setWpfj0102(wpfj0102);//总编号
						wpfj.setWpfj0103(wpfj0103);//评估报告名称
						wpfj.setType("3");
						wpfj.setWpfj0106(wpdj0129);
						wpfj01List.add(wpfj);
					}
					if((wpdj0130!=null&&wpdj0130.indexOf(wdName)>-1)||(wpdj0130==null&&wdName.equals(""))){
						wpfj = new Wpfj01();
						wpfj.setWpfj0102(wpfj0102);//总编号
						wpfj.setWpfj0103(wpfj0103);//评估报告名称
						wpfj.setType("4");
						wpfj.setWpfj0106(wpdj0130);
						wpfj01List.add(wpfj);
					}
					if((wpdj0131!=null&&wpdj0131.indexOf(wdName)>-1)||(wpdj0131==null&&wdName.equals(""))){
						wpfj = new Wpfj01();
						wpfj.setWpfj0102(wpfj0102);//总编号
						wpfj.setWpfj0103(wpfj0103);//评估报告名称
						wpfj.setType("5");
						wpfj.setWpfj0106(wpdj0131);
						wpfj01List.add(wpfj);
					}*/
				}else{///检索的某一类文档
					String tmpParam = "";
					switch(Integer.parseInt(wdType)){
					case 1:
						tmpParam = wpdj0127;
						break;
					case 2:
						tmpParam = wpdj0128;
						break;
					case 3:
						tmpParam = wpdj0129;
						break;
					case 4:
						tmpParam = wpdj0130;
						break;
					case 5:
						tmpParam = wpdj0131;
						break;
					}
					wpfj01List.add(getObject(wdName, tmpParam, wpfj0102, wpfj0103, wdType,wpfj0101));
				}
				//}
			}
		}catch (Exception e) {
			System.out.println(e);
		}
		
		
		return wpfj01List;
	}
	
	public String searchWptjByWpbgId(String id) {
		conn=JdbcUtil.getConnection();
		StringWriter xmlout = new StringWriter();
		String sql = "select * from wptj01 where wptj0106=? order by wptj0103";
		Document document=DocumentHelper.createDocument();
		Element fwlistElement =document.addElement("wptjList");
		
		try{
			ps = conn.prepareStatement(sql);
			ps.setString(1, id);
			rs=ps.executeQuery();
			Element fwElement;
			int i=0;
			while(rs.next()){
				fwElement=fwlistElement.addElement("wptjObjInfo");
				fwElement.addAttribute("WPTJ0102",rs.getString("WPTJ0102"));
				fwElement.addAttribute("WPTJ0103",rs.getString("WPTJ0103"));
				fwElement.addAttribute("WPTJ0104",rs.getString("WPTJ0104"));
				fwElement.addAttribute("WPTJ0105",rs.getString("WPTJ0105"));
				fwElement.addAttribute("WPTJ0106",rs.getString("WPTJ0106"));
				i++;
			}
			fwlistElement.addAttribute("size", String.valueOf(i));
			XMLWriter output = new XMLWriter(xmlout);
			output.write(document);
		}catch (Exception e) {
			System.out.println("拐点坐标查询失败");
		}
		return xmlout.toString();
	}

	/**
	 * 2013-5-8
	 * 崔艺
	 * @param wdName
	 * @param param
	 * @param wpfj0102
	 * @param wpfj0103
	 * @param wdType
	 * @param wpfj0101
	 * @return  
	 */
	private Wpfj01 getObject(String wdName,String param,String wpfj0102,String wpfj0103,String wdType,int wpfj0101){
		Wpfj01 wpfj = new Wpfj01();
		if((param!=null&&param.indexOf(wdName)>-1)||(param==null&&wdName.equals(""))){
			wpfj.setWpfj0101(wpfj0101);
			wpfj.setWpfj0102(wpfj0102);//总编号
			wpfj.setWpfj0103(wpfj0103);//评估报告名称
			wpfj.setType(wdType);
			wpfj.setWpfj0106(param);
		}
		return wpfj;
	}

	public Boolean updateWpfj(String wpfj0101, String wpfj0106, String type) {
		Boolean flag =false;
		   conn=JdbcUtil.getConnection();
		   String sql = "";
		   String column = getColumnByType(type);
		   sql = "update wpdj01 set " +column+"=?"+
		   		" where Wpdj0101=?";
			try {
				ps = conn.prepareStatement(sql);
				ps.setString(1, wpfj0106);
				ps.setString(2, wpfj0101);
				int number=ps.executeUpdate();
				if(number>0)
				{
					flag=true;
				}
				
			} catch (SQLException e) {
				e.printStackTrace();
			} finally
			{
				JdbcUtil.closeAll(ps,conn);
			}
			return flag;
	}
	private String getColumnByType(String type){
		String column = "";
		switch(Integer.parseInt(type)){
		case 1:
			column = "wpdj0127";
			break;
		case 2:
			column = "wpdj0128";
			break;
		case 3:
			column = "wpdj0129";
			break;
		case 4:
			column = "wpdj0130";
			break;
		case 5:
			column = "wpdj0131";
			break;
		}
		return column;
	}
}
